package com.ysd.dao.Impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


import com.ysd.entity.NewsCatalogue;
import com.ysd.entity.User;
import com.ysd.util.DBManager;

public class UserDaoImpl {
     //获取留言板信息
	public int addMessage(User user) {
		return DBManager.executeUpdate("insert into user(username,phone,email,content) values('"+user.getUsername()+"','"+user.getPhone()+"','"+user.getEmail()+"','"+user.getContent()+"')");
    	 
     }
	//修改留言状态
	public int updateMessageById(User user) {
		return DBManager.executeUpdate("update user set status='"+user.getStatus()+"' where id="+user.getId());
     }
	//修改回复
	public int updateMessageReplyById(User user) {
		return DBManager.executeUpdate("update user set status='已回复',reply='"+user.getReply()+"' where id="+user.getId());
     }
	//删除
	 public int delUser(int id) {
		 return DBManager.executeUpdate("DELETE FROM user WHERE id="+id);
	 }
	 //批量删除
	 public int deleteMassageByIds(String ids) {
		 String sql="DELETE FROM user where id in("+ids+")";
		 System.out.println(sql);
		 return DBManager.executeUpdate(sql);
	 }
	public List<User> getUserByStatus(String status,int page,int size) {
	    List<User> ulist=new ArrayList<User>();//创建List
	    Connection conn=DBManager.getConnection();//创建连接
    	Statement st=null;
    	ResultSet rs=null;
    	try {//正常情况
			st=conn.createStatement();//创建Stetement，作用是执行SQL语句
			String sql="SELECT * FROM  user WHERE 1=1";
			if(status!=null&&!status.equals("")) {
				sql+=" and status='"+status+"'";
			}
			sql +=" ORDER BY time DESC";
			sql +=" LIMIT "+(page-1)*size+","+size+"";
			System.out.println(sql);
			rs=st.executeQuery(sql);
			while(rs.next()) {
				int id=rs.getInt("id");
				String username=rs.getString("username");
				String phone=rs.getString("phone");
				String email=rs.getString("email");
				String content=rs.getString("content");
				String time=rs.getString("time");
				String sta=rs.getString("status");
				User user = new User(id, username, phone, email, content, time, sta);
				ulist.add(user);
				
			}
			return ulist;
		} catch (SQLException e) {//非正常情况
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
    }
	
	//查询所有留言
	public List<User> getUserByStatusPage(String status) {
	    List<User> ulist=new ArrayList<User>();//创建List
	    Connection conn=DBManager.getConnection();//创建连接
    	Statement st=null;
    	ResultSet rs=null;
    	try {//正常情况
			st=conn.createStatement();//创建Stetement，作用是执行SQL语句
			String sql="SELECT * FROM  user WHERE 1=1";
			if(status!=null&&!status.equals("")) {
				sql+=" and status='"+status+"'";
			}
			sql +=" ORDER BY time DESC";
			System.out.println(sql);
			rs=st.executeQuery(sql);
			while(rs.next()) {
				int id=rs.getInt("id");
				String username=rs.getString("username");
				String phone=rs.getString("phone");
				String email=rs.getString("email");
				String content=rs.getString("content");
				String time=rs.getString("time");
				String sta=rs.getString("status");
				User user = new User(id, username, phone, email, content, time, sta);
				ulist.add(user);
				
			}
			return ulist;
		} catch (SQLException e) {//非正常情况
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
    }
	public User getUserById(int id) {
	    User user=null;
	    Connection conn=DBManager.getConnection();//创建连接
    	Statement st=null;
    	ResultSet rs=null;
    	try {//正常情况
			st=conn.createStatement();//创建Stetement，作用是执行SQL语句
			String sql="SELECT * FROM  user WHERE id="+id;
			System.out.println(sql);
			rs=st.executeQuery(sql);
			while(rs.next()) {
				String username=rs.getString("username");
				String phone=rs.getString("phone");
				String email=rs.getString("email");
				String content=rs.getString("content");
				String time=rs.getString("time");
				String sta=rs.getString("status");
				user = new User(id, username, phone, email, content, time, sta);
	
			}
			return user;
		} catch (SQLException e) {//非正常情况
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
    }
}
